import json

import pymssql

from common.utils.exceptions import DatabaseExceptionError, InvalidParameterFormatError

from dbutils.pooled_db import PooledDB

class SqlServerClient:
    def __init__(self, db_config):
        self.result = {}
        self.conn = None
        self.cursor = None
        try:
            self.db_base = db_config if isinstance(db_config, dict) else json.loads(db_config)
            self.conn = pymssql.connect(server=self.db_base['host'], user=self.db_base['user'], port=self.db_base['port'], password=self.db_base['password'], database=self.db_base['database'])
            self.cursor = self.conn.cursor()



        except Exception as e:
            print("数据库连接失败")
            raise e

    def execute_sql(self, sql):
        if not sql:
            return
        try:
            for method, sql_data in sql.items():
                execute_method = getattr(self, f"_execute_{method}", None)
                if not execute_method:
                    InvalidParameterFormatError(sql, "sql字典集编写格式不符合规范")
                    raise ValueError("| Invalid SQL method")
                execute_method(sql_data)
            self.cursor.close()
            self.conn.close()
            return self.result

        except Exception as e:
            DatabaseExceptionError(sql, e)
            raise

    def _execute_write(self, sql_data):
        """
        执行通用的写入操作（INSERT、UPDATE、DELETE）
        """
        for sql_name, sql_ in sql_data.items():
            try:
                self.cursor.execute(str(sql_))
            except Exception as err:
                DatabaseExceptionError(sql_, err)
                raise err
        self.cursor.connection.commit()


    def _execute_select(self, sql_data):

        for sql_name, sql_ in sql_data.items():
            try:
                self.cursor.execute(sql_)
                self.result[sql_name] = [row[0] for row in self.cursor.fetchall()]
                self.fields = [field[0] for field in self.cursor.description]
                self.result[sql_name] = [dict(zip(self.fields, self.result[sql_name])) for result in  self.result[sql_name]]

            except Exception as err:
                DatabaseExceptionError(sql_, err)
                raise err


if __name__ == '__main__':
    sql_2 = {
        "select":
            {
                #"slect_1": "select username from sys_user;"

                 "select_1": "select approval_status from project_info where F_Id ='565497525033800069';"
            }

    }
    database_2 = {
        "host": "192.168.7.140",
        "port": 1433,
        "database": "tenant_JY972113",
        "user": "sa",
        "password": "Saas_jy140"
    }
    res = SqlServerClient(database_2).execute_sql(sql_2)
    print("数据执行结果", res)